همه ما در پروژه‌های مدیریت منابع انسانی یا محاسبات حقوق و دستمزد، با فرمت زمان سر و کار داشته‌ایم. تصور کنید مجموع ساعت کاری یک کارمند در ماه 120:30 (صد و بیست ساعت و سی دقیقه) شده است. حالا می‌خواهید این عدد را در نرخ دستمزد ساعتی ضرب کنید. اگر مستقیماً این کار را انجام دهید، به نتیجه‌ای عجیب و غریب می‌رسید!

بسیاری از کاربران تلاش می‌کنند با توابعی مثل LEFT یا MID ساعت و دقیقه را جدا کنند، اما اکسل لجبازی می‌کند. بیایید یک بار برای همیشه این گره را باز کنیم.

چرا اکسل با توابع متنی (TEXT Functions) میانه خوبی ندارد؟

دلیل اینکه توابع FIND یا MID روی سلول‌های با فرمت [h]:mm خروجی اشتباه می‌دهند این است که اکسل زمان را به صورت "متن" نمی‌بیند.

طبق تعریف مایکروسافت: "اکسل زمان را به عنوان کسر پله‌ای از یک روز (24 ساعت) ذخیره می‌کند. برای مثال، ساعت 12:00 ظهر معادل عدد 0.5 است، زیرا نیمی از یک روز کامل است." [Microsoft Support]

بنابراین وقتی شما 1:15 را می‌بینید، اکسل در واقع عدد 0.052083 را در دل خود نگه داشته است. توابع متنی روی ظاهر سلول کار نمی‌کنند، بلکه روی مقدار واقعی (Value) اثر می‌گذارند.

آموزش اکسل

راه حل نهایی: فرمول جادویی عدد ۲۴

برای تبدیل هر زمان به معادل اعشاری آن، تنها کافیست از یک فرمول ساده ریاضی استفاده کنید. چون هر واحد زمانی در اکسل معادل یک روز است و هر روز ۲۴ ساعت دارد، فرمول به این صورت خواهد بود:

= A1 * 24

نکته حیاتی: بعد از زدن این فرمول، اکسل به صورت خودکار فرمت سلول جدید را هم روی Time می‌گذارد. شما باید وارد تنظیمات Format Cells شده و نوع سلول را روی Number یا General قرار دهید.

مثال کاربردی:

اگر در سلول A1 مقدار 1:15 را دارید:

  1. در سلول B1 بنویسید: =A1*24
  2. فرمت سلول B1 را به Number تغییر دهید.
  3. خروجی دقیقاً 1.25 خواهد بود.
// Calculating total pay based on hours
// Cell A1: 10:30 (Work Duration)
// Cell A2: 50000 (Hourly Rate in Tomans)

= A1 * 24 * A2 
// Result: 525000

چرا از [h] استفاده می‌کنیم؟

در سوالتان به فرمت [h]:mm اشاره کردید. این کروشه دور ساعت به اکسل می‌فهماند که ساعت را "تجمعی" نشان دهد. اگر کروشه نباشد، به محض اینکه جمع ساعت‌ها از ۲۴ رد شود، شمارنده صفر می‌شود (مثل ساعت دیواری). اما با [h]، اکسل می‌تواند مثلاً ۱۵۰:۳۰ را به درستی نمایش دهد.